#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : modify_price.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-06-03 09:25
@Desc    : 智能调价 功能
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import json
import pandas as pd
import requests
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import mail_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

if debug:
    receiver = 'bianzhiwei@iyoujia.com'  # ;xiaoyaoshuangxi@iyoujia.com;lilei@iyoujia.com
    # url = 'http://testmis.iyoujia.com/mis/lodge/price/intelligentPrice'
    url = 'http://premis.iyoujia.com/mis/lodge/price/intelligentPrice'
else:
    receiver = 'zhanglin@iyoujia.com;liusimeng@iyoujia.com;dt@iyoujia.com'
    url = 'http://mis.iyoujia.com/mis/lodge/price/intelligentPrice'


def curtime_online_price():
    sql = """select 
        concat(lh.id,"") 房屋id,
        concat(l.id,"")  lodge_id,
        if (lpb.sales_channel_id = 20,
        concat('https://www.airbnb.cn/rooms/',aul.listing_id),
        concat('https://www.tujia.com/detail/',ltl.third_lodge_id,'.htm')) 房源所在渠道网址,
        lpb.sales_channel_id, 
        concat(ifnull(lps.price,CASE
                                WHEN  (FIND_IN_SET(DATE_FORMAT(curdate(),'%%w'),
                                                  (CASE 
                                                   WHEN lpw.weekend_type=1 THEN '5,6,0' 
                                                   WHEN lpw.weekend_type=2 THEN '5,6' 
                                                   WHEN lpw.weekend_type=3 THEN '6,0' 
                                                   ELSE '999' 
                                                   END))>0 AND lpw.price IS NOT NULL AND lpw.price > 0) 
                                THEN lpw.price
                                ELSE lpb.price
                                END ),"") 原始价格    

        from youjia.lod_house lh 
        left join youjia.lodge l on l.id = lh.lodge_id
        left join youjia.lod_price_base lpb on l.id =lpb.lodge_id -- and lpb.sales_channel_id = 4
        left join youjia_common.sales_channel sc on sc.id = lpb.sales_channel_id
        left join youjia.lod_price_week lpw on l.id= lpw.lodge_id  and lpb.sales_channel_id = lpw.sales_channel_id
        left join youjia.lod_price_special lps on l.id= lps.lodge_id and lpb.sales_channel_id = lps.sales_channel_id  
                                                                     and lps.dt = curdate() and lps.is_delele = 0
        left join youjia.lod_third_lodge ltl on ltl.lodge_id = l.id and ltl.sales_channel_id =  lpb.sales_channel_id   
        left join youjia.airbnb_upload_lodge aul on aul.lodge_id = l.id
        where l.city_id <> 376
        and lh.city_id <> 376
        and lh.state = 100
        and lh.service_id  = 30320
        and l.state = 0
        and l.state_super = 10
        and lpb.sales_channel_id in (3,20)
        group by lh.id,l.id,lpb.sales_channel_id ;"""
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def cur_month_cover_ratio():
    """
    获取本月符合条件的所有房屋
    :return:
    """
    month_et = date_util.curdate()
    month_st = date_util.cur_month_first(dt=month_et)
    temp_month_days = date_util.date_sub_date(month_st, month_et)
    if temp_month_days <= 10:
        month_st = date_util.date_sub(month_et, days=15)
    sql = """select 
                concat(coalesce(whc.house_id, 'avg_rate'),"") 房屋id,
                round(ifnull(sum(whc.income)/sum(whc.rent_day),0),2) cur_month_cover_rate
                from youjia_report.wh_house_static whs 
                left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
                where whc.dt between '{month_st}' and '{month_et}'
                and whs.service_id = 30320
                and whs.house_state = 100
                group by whc.house_id
                with rollup
                """.format(month_st=month_st, month_et=month_et)
    df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)

    avg_df = df[df['房屋id'] == 'avg_rate']
    avg_df = avg_df.set_index('房屋id')
    avg_json = json.loads(avg_df.to_json(orient='records'))
    cur_month_cover_rate = avg_json[0]['cur_month_cover_rate']
    print(cur_month_cover_rate)
    df1 = df[(df.cur_month_cover_rate >= cur_month_cover_rate) & (df['房屋id'] != 'avg_rate')]
    df2 = df[(df.cur_month_cover_rate < cur_month_cover_rate) & (df['房屋id'] != 'avg_rate')]
    return df1, df2


def rent_coverage_ratio():
    """
    获取所有房屋 半年 的房租覆盖比
    :return:
    """
    et = date_util.last_month_last(dt=date_util.curdate())
    st = date_util.date_sub(date_util.cur_month_first(dt=date_util.curdate()), months=6, days=0)

    sql = """select 
                concat(whc.house_id,"") 房屋id,
                date_format(whc.dt,'%%Y年%%m月') 月份批次,
                1 房屋次数, 
                round(ifnull(sum(whc.income)/sum(whc.rent_day),0),2) 房租覆盖比
                from youjia_report.wh_house_static whs 
                left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
                where dt between '{st}' and '{et}'
                and whs.service_id = 30320
                and whs.house_state = 100
                group by whc.house_id,date_format(whc.dt,'%%Y-%%m')
            """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def house_rent_score():
    """
    房屋出租率得分
    :return:
    """
    sql1 = """select 
        concat(whc.house_id ,'') 房屋id,
        ifnull(sum(have_order)/sum(if (online_stock = 1 and rent_stock = 1,1,0)),0)*0.35 过去三天得分
        from youjia_report.wh_house_calendar whc
        where dt between date_sub(curdate(),interval 3 day) and date_sub(curdate(),interval 1 day)
        group by whc.house_id;"""
    sql2 = """select 
        concat(whc.house_id ,'') 房屋id,
        ifnull(sum(have_order)/sum(if (online_stock = 1 and rent_stock = 1,1,0)),0)*0.3 过去七天得分
        from youjia_report.wh_house_calendar whc
        where dt between date_sub(curdate(),interval 7 day) and date_sub(curdate(),interval 1 day)
        group by whc.house_id;"""
    sql3 = """select 
        concat(whc.house_id ,'') 房屋id,
        ifnull(sum(have_order)/sum(if (online_stock = 1 and rent_stock = 1,1,0)),0)*0.35 未来三天得分
        from youjia_report.wh_house_calendar whc
        where dt between curdate() and date_add(curdate(),interval 2 day)
        group by whc.house_id;"""

    df1 = pd.read_sql_query(sql1, db_util.youjia_13_sjz_db)
    df2 = pd.read_sql_query(sql2, db_util.youjia_13_sjz_db)
    df = pd.merge(df1, df2, on='房屋id')
    df3 = pd.read_sql_query(sql3, db_util.youjia_13_sjz_db)
    df = pd.merge(df, df3, on='房屋id')
    df['总得分'] = df['过去三天得分'] + df['过去七天得分'] + df['未来三天得分']
    return df


def process_level():
    """
    处理房源级别
    :return:
    """

    rent_all_df = rent_coverage_ratio()
    cur_month_cover_df, cur_month_not_cover_df = cur_month_cover_ratio()

    # 本月房租覆盖比 合格的 A 级别的
    rent_cover_df = rent_all_df[rent_all_df.房租覆盖比 >= 1].copy()
    house_cover_df = rent_cover_df.groupby('房屋id').agg({'房屋次数': 'sum'})
    house_cover_df = house_cover_df.reset_index()
    house_cover_df = house_cover_df[house_cover_df.房屋次数 >= 3]
    # 获取本月房租完成度 大于等于 当月平均值

    df_A = pd.merge(house_cover_df, cur_month_cover_df, on='房屋id', how='inner')
    df_A = df_A.reset_index()
    df_A = df_A[['房屋id']]
    df_A['房屋级别'] = 'A'
    log.info('处理级别 A 的房源数据完成')
    log.info(df_A.shape)

    df_B = pd.merge(cur_month_cover_df, df_A, on='房屋id', how='left')
    df_B = df_B[df_B.房屋级别.isnull()]
    df_B = df_B.reset_index()
    df_B = df_B[['房屋id']]
    df_B['房屋级别'] = 'B'
    log.info('处理级别 B 的房源数据完成')
    log.info(df_B.shape)

    df_C = cur_month_not_cover_df[['房屋id']]
    df_C['房屋级别'] = 'C'
    log.info('处理级别 C 的房源数据完成')
    log.info(df_C.shape)

    df_All = pd.concat([df_A, df_B, df_C], axis=0)
    log.info('合并 级别 A、B、C 的房源数据完成')
    log.info(df_All.shape)

    # print(df_A.shape, df_B.shape, df_C.shape, df_All.shape)

    curtime_online_price_df = curtime_online_price()
    curtime_df = pd.merge(curtime_online_price_df, df_All, how='left', on='房屋id')
    return curtime_df


def process_price(row):
    house_levl = row['房屋级别']
    house_score = float(row['总得分'])
    price = float(row['原始价格'])
    if house_score >= 0.633:
        if house_levl == 'A':
            price = price + price * 0.1
        elif house_levl == 'B':
            price = price + price * 0.05
        else:
            price = price
    elif 0.402 <= house_score < 0.633:
        if house_levl == 'A':
            price = price
        elif house_levl == 'B':
            price = price - price * 0.05
        else:
            price = price - price * 0.1
    else:
        if house_levl == 'A':
            price = price - price * 0.05
        elif house_levl == 'B':
            price = price - price * 0.1
        else:
            price = price - price * 0.2
    return price


def is_have(dt):
    log.info('正在查询modify_price表中是否有今天的数据' )
    sql = "select id from youjia_report.modify_price where dt='{dt}'".format(dt=dt)
    df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    if len(df) > 0:
        log.info('表中今天已经有数据了')
        return True
    return False


def transfer_data_to_youjia(dt):
    sql = """ select 
            id,
            concat(lodge_id,"") lodge_id,
            dt,
            channel_id,
            round(original_price) original_price,
            round(final_price) final_price
            from youjia_report.modify_price 
            where dt = '{dt}'
            and is_push <> 1
            -- and id = 59683
            -- and is_push = 0
           """.format(dt=dt)
    df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    if df.__len__() == 0:
        log.info('改价里面数据全部推送完毕！13.youjia_report.modify_price 表')
    update_sql = 'update youjia_report.modify_price set is_push = {} where  id={};'
    message_sql = 'update youjia_report.modify_price set is_push = {} ,message= "{}" where  id={};'

    with db_util.get_connection(test=False) as conn:
        for idx, row in df.iterrows():
            modify_price_id = row['id']
            lodge_id = row['lodge_id']
            dt = row['dt']
            channel_id = row['channel_id']
            original_price = row['original_price']
            final_price = row['final_price']
            data = {'channelId': channel_id,
                    'lodgeId': lodge_id,
                    'price': final_price,
                    'date': dt}
            resp = requests.post(url=url, data=data)
            if resp and 'code' in resp.json() and resp.json()['code'] == 1:
                log.info('房源 {} 渠道 {} 改价成功 {} - {} '.format(lodge_id, channel_id, original_price, final_price))
                conn.query(update_sql.format(1, modify_price_id))
            else:
                log.warning('房源 {} 渠道 {} 改价失败 {} - {} '.format(lodge_id, channel_id, original_price, final_price))
                message = ''
                if resp and 'message' in resp.json():
                    message = resp.json()['message']
                    log.warning(message)
                else:
                    log.warning('为止错误')

                conn.query(message_sql.format(2, message, modify_price_id))


def get_mail_df(dt):
    dt = dt if dt else date_util.curdate(0)
    sql = """SELECT 
                concat(house_id,"") 房屋id,
                concat(lodge_id,"") 房源id,
                dt 日期,
                url 网址,
                IF(channel_id = 3, '途家', 'Airbnb') 渠道,
                house_level 房源级别,
                score 房源得分,
                original_price 原始价格,
                final_price '修改之后的价格',
                IF(is_push = 1, '推送成功', '推送失败') 是否推送成功,
                message 失败原因
                FROM youjia_report.modify_price
                where dt = '{dt}'
                ;""".format(dt=dt)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def process(dt=None):
    dt = dt if dt else date_util.curdate(0)
    house_level_df = process_level()
    house_rent_score_df = house_rent_score()
    df = pd.merge(house_level_df, house_rent_score_df, how='left', on='房屋id')
    df['调整之后价格'] = df.apply(lambda row: process_price(row), axis=1)
    # mail_df = df.copy()
    df = df[['房屋id', 'lodge_id', '房源所在渠道网址', 'sales_channel_id', '原始价格', '房屋级别', '总得分', '调整之后价格']]
    df.columns = ['house_id', 'lodge_id', 'url', 'channel_id', 'original_price', 'house_level', 'score', 'final_price']
    df['dt'] = dt
    df['create_time'] = date_util.datetime_now()
    df['modify_time'] = date_util.datetime_now()
    if not is_have(dt):
        df.to_sql(name='modify_price', schema='youjia_report', con=db_util.youjia_13_sjz_db, if_exists='append',
                  index=False)
        log.info('存储数据库成功！')

    # todo 将数据传输给有家 业务
    transfer_data_to_youjia(dt)
    # if debug:
    #     excel_util.pd_to_excel({"调价": mail_df}, '调价')
    # else:
    text = """    Dear All:
                        附件是 调价数据！ 
                        请查收！
                        谢谢！
                如有问题！
                请联系: dt@iyoujia.com
                """
    mail_df = get_mail_df(dt)
    mail_util.distribute_mail(df_dict={"房源调价报表": mail_df}, file_name='调价', text=text,
                              title="房源--调价", receiver=receiver,
                              na_rep='-', index=False)


def run():
    try:
        process()
        # transfer_data_to_youjia('2019-06-05')
    except Exception as e:
        mail_util.error_mail('调价出问题了', e.__str__())
        log.exception(e)


if __name__ == '__main__':
    run()
